package zy.dao.base.stream.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.stream.StreamBillDAO;
import zy.entity.base.stream.T_Base_Stream_Bill;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class StreamBillDAOImpl extends BaseDaoImpl implements StreamBillDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object seb_se_code = params.get("seb_se_code");
		Object seb_number = params.get("seb_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_base_stream_bill t");
		sql.append(" JOIN t_base_stream se ON se_code = seb_se_code AND se.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND seb_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND seb_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(seb_se_code)) {
			sql.append(" AND seb_se_code = :seb_se_code ");
		}
		if (StringUtil.isNotEmpty(seb_number)) {
			sql.append(" AND INSTR(seb_number,:seb_number) > 0 ");
		}
		sql.append(" AND se_sp_code = :shop_code ");//上级店铺编号
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Base_Stream_Bill> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object seb_se_code = params.get("seb_se_code");
		Object seb_number = params.get("seb_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT seb_id,seb_se_code,seb_number,seb_date,seb_money,seb_discount_money,seb_payable,seb_payabled,seb_pay_state,");
		sql.append(" seb_join_number,seb_type,seb_sysdate,t.companyid,se_name AS stream_name");
		sql.append(" FROM t_base_stream_bill t");
		sql.append(" JOIN t_base_stream se ON se_code = seb_se_code AND se.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND seb_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND seb_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(seb_se_code)) {
			sql.append(" AND seb_se_code = :seb_se_code ");
		}
		if (StringUtil.isNotEmpty(seb_number)) {
			sql.append(" AND INSTR(seb_number,:seb_number) > 0 ");
		}
		sql.append(" AND se_sp_code = :shop_code ");//上级店铺编号
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY seb_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Stream_Bill.class));
	}
	
	@Override
	public T_Base_Stream_Bill loadByJoinNumber(String joinNumber, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT seb_id,seb_se_code,seb_number,seb_pay_state");
		sql.append(" FROM t_base_stream_bill t");
		sql.append(" WHERE seb_join_number = :seb_join_number AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("seb_join_number", joinNumber).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Stream_Bill.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void save(T_Base_Stream_Bill bill) {
		String prefix = CommonUtil.NUMBER_PREFIX_WLD + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(seb_number))) AS new_number");
		sql.append(" FROM t_base_stream_bill");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(seb_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", bill.getCompanyid()), String.class);
		bill.setSeb_number(new_number);
		sql.setLength(0);
		sql.append("INSERT INTO t_base_stream_bill");
		sql.append(" (seb_se_code,seb_number,seb_date,seb_money,seb_discount_money,seb_payable,seb_payabled,seb_pay_state,seb_join_number,seb_type,seb_sysdate,companyid)");
		sql.append(" VALUES");
		sql.append(" (:seb_se_code,:seb_number,:seb_date,:seb_money,:seb_discount_money,:seb_payable,:seb_payabled,:seb_pay_state,:seb_join_number,:seb_type,:seb_sysdate,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(bill),holder);
		bill.setSeb_id(holder.getKey().intValue());
	}

	@Override
	public void delete(T_Base_Stream_Bill bill) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_base_stream_bill");
		sql.append(" WHERE seb_number=:seb_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(bill));
	}

	@Override
	public void delete(Integer seb_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_base_stream_bill");
		sql.append(" WHERE seb_id=:seb_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("seb_id", seb_id));
	}
	
}
